package dominio;

import interfaces.BaseDeDatos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.PreparedStatement;

public class Reserva implements BaseDeDatos {
	protected Integer idReserva;
	protected PaqueteDeViaje pdv;
	protected String fecha;
	protected Integer pasajeros;
	protected Float precioTotal;
	protected List<Cliente> clientes;

	public Reserva() {

	}

	public Reserva(Integer idReserva, PaqueteDeViaje pdv, String fecha,
			Integer pasajeros, Float precioTotal, List<Cliente> clientes) {
		this.idReserva = idReserva;
		this.pdv = pdv;
		this.fecha = fecha;
		this.pasajeros = pasajeros;
		this.precioTotal = precioTotal;
		this.clientes = clientes;
	}

	public Integer getIdReserva() {
		return idReserva;
	}

	public void setIdReserva(Integer idReserva) {
		this.idReserva = idReserva;
	}

	public PaqueteDeViaje getPdv() {
		return pdv;
	}

	public void setPdv(PaqueteDeViaje pdv) {
		this.pdv = pdv;
	}

	public String getFecha() {
		return fecha;
	}

	public void setFecha(String fecha) {
		this.fecha = fecha;
	}

	public Integer getPasajeros() {
		return pasajeros;
	}

	public void setPasajeros(Integer pasajeros) {
		this.pasajeros = pasajeros;
	}

	public Float getPrecioTotal() {
		return precioTotal;
	}

	public void setPrecioTotal(Float precioTotal) {
		this.precioTotal = precioTotal;
	}

	public List<Cliente> getClientes() {
		return clientes;
	}

	public void setClientes(List<Cliente> clientes) {
		this.clientes = clientes;
	}
	
	public String getDestino(){
		return pdv.getNombreAeropuertoArrivo();
	}
	
	public String getUrlPaquete(){
		return "&paquete=" + pdv.getIdPaquete();
	}
	
	public String getUrlClientes(){
		String urlCliente = "";
		for (Cliente c : clientes) {
			urlCliente += "&cliente=" + c.idCliente;
		}
		return urlCliente;
	}

	@Override
	public List<Reserva> leerRegistrosBD() throws SQLException {
		Connection conn = null;
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		conn = DriverManager.getConnection(host + dbName, user, pass);
		Statement st = conn.createStatement();
		Statement stClientes = conn.createStatement();

		List<Reserva> reservas = new ArrayList<Reserva>();

		String query = "select idreservas_r, idpaquetedeviaje_r, "
				+ "fecha_r, cantidadpasajeros_r, preciototal_r from reservas ";
		ResultSet rs = st.executeQuery(query);
		while(rs.next()){
				// obtener paquete de viaje
				PaqueteDeViaje pdv = new PaqueteDeViaje().leerRegistroBD(
						rs.getInt("idpaquetedeviaje_r"));
				
				// obtener lista clientes
				List<Cliente> clientes = new ArrayList<Cliente>();		
				String queryClientes = "select idcliente_cxr from clientes_x_reserva "
						+ "where idreserva_cxr = " + rs.getInt("idreservas_r") + ";";
				ResultSet rsClientes = stClientes.executeQuery(queryClientes);
				while(rsClientes.next()){
							clientes.add(new Cliente().leerRegistroBD(
									rsClientes.getInt("idcliente_cxr"))
									);
						;}
				
				// obtener reserva
				reservas.add(new Reserva(
							rs.getInt("idreservas_r"),
							pdv,
							rs.getString("fecha_r"),
							rs.getInt("cantidadpasajeros_r"),
							rs.getFloat("preciototal_r"),
							clientes
						)
					)
				;}
	
		if(conn != null)conn.close();
		return reservas;
	}

	@Override
	public void guardarRegistroBd() throws SQLException {
		Connection conn = null;
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		conn = DriverManager.getConnection(host + dbName, user, pass);
		conn.setAutoCommit(false);
		Statement st = conn.createStatement();
		try {
			// guardar reserva
			String query = "INSERT INTO reservas(idpaquetedeviaje_r,"
					+ "fecha_r,cantidadpasajeros_r,preciototal_r)VALUES("
					+ this.pdv.getIdPaquete() + ",'" + this.fecha + "',"
					+ this.pasajeros + "," + this.precioTotal + ");";
			st.executeUpdate(query);

			// guardar clientes_x_reserva
			for (Cliente cliente : clientes) {
				query = "INSERT INTO clientes_x_reserva(idreserva_cxr, idcliente_cxr)VALUES("
						+ "(select max(idreservas_r) from reservas)"
						+ ","
						+ cliente.getIdCliente() + ");";
				st.executeUpdate(query);
			}

			// actualizar paquetedeviaje
			query = "UPDATE paquetesdeviaje SET completo_pdv = "
					+ (pdv.Completo - pasajeros)
					+ " WHERE idpaquetedeviaje_pdv = " + pdv.idPaquete + ";";
			st.executeUpdate(query);	
			conn.commit();

		} catch (SQLException e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			conn.setAutoCommit(true);
			conn.close();
		}

	}

	@Override
	public Reserva leerRegistroBD(int id) throws SQLException {
		PaqueteDeViaje pdv = new PaqueteDeViaje();;	
		Connection conn = null;
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		conn = DriverManager.getConnection(host + dbName, user, pass);
		Statement st = conn.createStatement();
		
		List<Integer> idsClientes = new ArrayList<Integer>();
		List<Cliente> clientes = new ArrayList<Cliente>();
		Reserva reserva = new Reserva();
		reserva.setIdReserva(id);
	
		
		String query = "select idcliente_cxr from clientes_x_reserva "
				+ "where idreserva_cxr = " + id + ";";
		ResultSet rs = st.executeQuery(query);
		while(rs.next()){
					idsClientes.add(rs.getInt("idcliente_cxr"));
				;}
		
		query = "select idpaquetedeviaje_r, fecha_r, cantidadpasajeros_r, preciototal_r from reservas "
				+ "where idreservas_r = " + id + ";";
		rs = st.executeQuery(query);
		while(rs.next()){
					reserva.setPdv(pdv.leerRegistroBD(rs.getInt("idpaquetedeviaje_r")));
					reserva.setFecha(rs.getString("fecha_r"));
					reserva.setPasajeros(rs.getInt("cantidadpasajeros_r"));
					reserva.setPrecioTotal(rs.getFloat("preciototal_r"));
				;}
		
		for (Integer idc : idsClientes) {
			Cliente c = new Cliente();
			c.leerRegistroBD(idc);
			clientes.add(c);		
		}
		
		reserva.setClientes(clientes);
			
		if(conn != null)conn.close();
		return reserva;
	}

	@Override
	public void actualizarRegistroBd() throws SQLException {
		Reserva reservaVieja = new Reserva().leerRegistroBD(idReserva);
		Connection conn = null;
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		conn = DriverManager.getConnection(host + dbName, user, pass);
		conn.setAutoCommit(false);
		Statement st = conn.createStatement();
		try {
			// guardar reserva
			String query = "UPDATE reservas"
					+ " SET idpaquetedeviaje_r = " + this.pdv.getIdPaquete()
					+ ", cantidadpasajeros_r = " + this.pasajeros
					+ ", preciototal_r = " + this.precioTotal 
					+ " WHERE idreservas_r = " + idReserva;
			st.executeUpdate(query);

			// guardar clientes_x_reserva
			query = "DELETE from clientes_x_reserva where idreserva_cxr = " 
							+ idReserva;
					st.executeUpdate(query);			
			
			for (Cliente cliente : clientes) {
					query = "INSERT INTO clientes_x_reserva(idreserva_cxr, idcliente_cxr)VALUES("
							+ idReserva	+ ","
							+ cliente.getIdCliente() + ");";
					st.executeUpdate(query);			
			}

			// actualizar paquetedeviaje
			if(pdv.idPaquete == reservaVieja.pdv.idPaquete){
				query = "UPDATE paquetesdeviaje SET completo_pdv = "
						+ (pdv.Completo - ( pasajeros - reservaVieja.pasajeros ))
						+ " WHERE idpaquetedeviaje_pdv = " + pdv.idPaquete + ";";
				st.executeUpdate(query);	
			} else {
				query = "UPDATE paquetesdeviaje SET completo_pdv = "
						+ ( pdv.Completo - pasajeros )
						+ " WHERE idpaquetedeviaje_pdv = " + pdv.idPaquete + ";";
				st.executeUpdate(query);
				
				query = "UPDATE paquetesdeviaje SET completo_pdv = "
						+ ( reservaVieja.pdv.Completo + reservaVieja.pasajeros )
						+ " WHERE idpaquetedeviaje_pdv = " + reservaVieja.pdv.idPaquete + ";";
				st.executeUpdate(query);
			}
			conn.commit();

		} catch (SQLException e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			conn.setAutoCommit(true);
			conn.close();
		}
		
	}
}
